﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using CommonLibrary.Data.Model;

namespace CommonLibrary.Data.Sql
{
    public class SqlPublicDAL
    {
        #region 临时表

        /// <summary>
        /// 获取唯一临时表名
        /// </summary>
        /// <returns></returns>
        public static string GetTempTableName(string in_TableName)
        {
            //tempdb.dbo.TccsTemp_2011_10_19_14_27_07_363_00000000000956685853_GetRandName
            string tableName = "tempdb.dbo.TccsTemp_";
            tableName += DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss_fff") + "_";
            tableName += Guid.NewGuid().ToString().ToUpper().Replace("-", "_");
            if (string.IsNullOrEmpty(in_TableName) == false)
            {
                tableName += "_" + in_TableName;
            }
            return tableName.ClLeft(128);//临时表名最长128
        }

        #endregion


        /// <summary>
        /// 从Excel文件中生成DataTable
        /// </summary>
        /// <param name="path"></param>
        /// <param name="isFirstRowHeader"></param>
        /// <returns></returns>
        public static DataTable GetDataTableFromCsvOLEDB(string path, bool isFirstRowHeader)
        {
            string header = isFirstRowHeader ? "Yes" : "No";
            string pathOnly = Path.GetDirectoryName(path);
            string fileName = Path.GetFileName(path);
            string sql = @"SELECT * FROM [" + fileName + "]";

            using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + ";Extended Properties=\"Text;HDR=" + header + "\""))
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            {
                DataTable dataTable = new DataTable();
                dataTable.Locale = CultureInfo.CurrentCulture;
                adapter.Fill(dataTable);
                return dataTable;
            }
        }


        #region GetConnectionString

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_FullName"></param>
        /// <returns></returns>
        public static DataTable GetConnectionTable(string in_FullName)
        {
            if (File.Exists(in_FullName) == false)
            {
                throw new Exception(in_FullName + "不存在！");
            }

            DataSet ds = new DataSet();
            ds.ReadXml(in_FullName);
            if (ds.Tables[0].ClIsNullOrEmpty())
            {
                throw new Exception(in_FullName + "文件为空！");
            }

            return ds.Tables[0];
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_FullName"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static string GetConnectionStringFull(string in_FullName, int index)
        {
            DataTable dt = GetConnectionTable(in_FullName);
            if (index >= dt.Rows.Count)
            {
                throw new Exception(index.ToString() + "大于下限值！");
            }
            DataRow row = dt.Rows[index];

            return "server=" + Convert.ToString(row["server"]) + ";database=" + Convert.ToString(row["database"]) + ";uid=" + Convert.ToString(row["uid"]) + ";pwd=" + Convert.ToString(row["pwd"]).ClDecrypt3DES() + ";";
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_FullName"></param>
        /// <returns></returns>
        public static string GetConnectionStringFull(string in_FullName)
        {
            return GetConnectionStringFull(in_FullName, 0);
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_FullName"></param>
        /// <param name="in_Description"></param>
        /// <returns></returns>
        public static string GetConnectionStringFull(string in_FullName, string in_Description)
        {
            DataTable dt = GetConnectionTable(in_FullName);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (Convert.ToString(dt.Rows[i]["description"]).ClToUpperTrim() == in_Description.ClToUpperTrim())
                {
                    return GetConnectionStringFull(in_FullName, i);
                }
            }

            throw new Exception(in_Description + "配置不存在！");
        }

        /// <summary>
        /// 获取连接文件
        /// </summary>
        /// <param name="in_File"></param>
        /// <returns></returns>
        public static string GetConnectionFile(string in_File)
        {
            string str = @"D:\INI\" + in_File;
            if (File.Exists(str))
            {
                return str;
            }

            str = FileClass.GetStartupPath() + in_File;
            if (File.Exists(str))
            {
                return str;
            }

            throw new Exception(in_File + "不存在！");
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_File"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static string GetConnectionString(string in_File, int index)
        {
            return GetConnectionStringFull(GetConnectionFile(in_File), index);
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_File"></param>
        /// <returns></returns>
        public static string GetConnectionString(string in_File)
        {
            return GetConnectionStringFull(GetConnectionFile(in_File));
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="in_File"></param>
        /// <param name="in_Description"></param>
        /// <returns></returns>
        public static string GetConnectionString(string in_File, string in_Description)
        {
            return GetConnectionStringFull(GetConnectionFile(in_File), in_Description);
        }

        /// <summary>
        /// 获取连接字符串中的描述
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <returns></returns>
        public static string GetConnectionDescription(string in_ConnectionString)
        {
            string[] arr = in_ConnectionString.ClSplitString(";");
            return arr[0];
        }

        /// <summary>
        /// 获取连接字符串中的服务器
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <returns></returns>
        public static string GetConnectionServer(string in_ConnectionString)
        {
            string[] arr = in_ConnectionString.ClSplitString(";");
            return arr[1];
        }

        /// <summary>
        /// 获取连接字符串中的数据库
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <returns></returns>
        public static string GetConnectionDataBase(string in_ConnectionString)
        {
            string[] arr = in_ConnectionString.ClSplitString(";");
            return arr[2];
        }

        /// <summary>
        /// 获取连接字符串中的长名称
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <returns></returns>
        public static string GetConnectionDbFullName(string in_ConnectionString)
        {
            return "[" + GetConnectionServer(in_ConnectionString) + "].[" + GetConnectionDataBase(in_ConnectionString) + "].dbo.";
        }

        /// <summary>
        /// 生成连接文件
        /// </summary>
        public static void ShowConnectionFileDemo()
        {
            #region MyRegion

            string str = @"<NewDataSet>
  <Table>
    <description>GZ</description>
    <server>GZServer</server>
    <database>master</database>
    <uid>jian</uid>
    <pwd>使用3DES加密xSs0QEPlvCPdZrD6G6Br1Q==</pwd>    
  </Table>
  <Table>
    <description>SH</description>
    <server>sh_server</server>
    <database>hr</database>
    <uid>jian</uid>
    <pwd>xSs0QEPlvCPdZrD6G6Br1Q==</pwd>    
  </Table>
</NewDataSet>";


            #endregion

            string txfile = FileClass.GetTempFile(".xml");
            using (StreamWriter sw = new StreamWriter(txfile, true, Encoding.UTF8))
            {
                sw.Write(str);
            }
            ("notepad.exe " + txfile).ClRunAsDos();
        }

        #endregion


        /// <summary>
        /// 字典转为参数
        /// </summary>
        /// <param name="in_action"></param>
        /// <param name="in_dic"></param>
        /// <param name="out_paras"></param>
        /// <param name="out_sql"></param>
        /// <returns></returns>
        public static bool DictionaryToParameters(string in_action, Dictionary<string, object> in_dic, out SqlParameter[] out_paras, out string out_sql)
        {
            string str = "";
            List<SqlParameter> paras = new List<SqlParameter>();
            foreach (KeyValuePair<string, object> keyValuePair in in_dic)
            {
                if (keyValuePair.Key == "RecordStatus")
                {
                    continue;
                }
                if (keyValuePair.Key == "RecordFlag")
                {
                    continue;
                }
                if (keyValuePair.Key == "RecordSignId")
                {
                    continue;
                }
                if (keyValuePair.Key == "RecordSignMsg")
                {
                    continue;
                }
                if (in_action == "select")
                {
                    str += "  and [" + keyValuePair.Key + "] = @" + keyValuePair.Key;
                    paras.Add(new SqlParameter("@" + keyValuePair.Key, keyValuePair.Value));
                }
                if (in_action == "delete")
                {
                    str += "  and [" + keyValuePair.Key + "] = @" + keyValuePair.Key;
                    paras.Add(new SqlParameter("@" + keyValuePair.Key, keyValuePair.Value));
                }
                if (in_action == "update")
                {
                    paras.Add(new SqlParameter("@" + keyValuePair.Key, keyValuePair.Value));
                }
                if (in_action == "insert")
                {
                    str += ", [" + keyValuePair.Key + "]";
                    paras.Add(new SqlParameter("@" + keyValuePair.Key, keyValuePair.Value));
                }
            }
            out_sql = str.Substring(2);
            out_paras = paras.ToArray();
            return true;
        }

        /// <summary>
        /// 获取表信息
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <returns></returns>
        public static List<ModelSqlServerTable> GetTablesInfo(string in_ConnectionString)
        {
            DataTable dtSourceTables = ClSql.ExecuteDataTable(in_ConnectionString, CommandType.StoredProcedure, "sp_tables", new SqlParameter[] { new SqlParameter("@table_type", "'''TABLE'''") });
            return DataExtensions.DataTableToList<ModelSqlServerTable>(dtSourceTables);
        }

        /// <summary>
        /// 获取列信息
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <returns></returns>
        public static List<ModelSqlServerColumn> GetColumnsInfo(string in_ConnectionString, string in_table)
        {
            DataTable dtSourceCol = ClSql.ExecuteDataTable(in_ConnectionString, CommandType.StoredProcedure, "sp_columns", new SqlParameter[] { new SqlParameter("@table_name", in_table) });
            return DataExtensions.DataTableToList<ModelSqlServerColumn>(dtSourceCol);
        }
    }
}
